import decimal

from dbutils.pooled_db import PooledDB
from pymysql.cursors import DictCursor

from db_pool import DBDevice, SF_DB
from x_pm import Config

"""
读取数据用
"""


class LocTemplate(DBDevice):
    id: int = None
    single_length: int = None
    single_width: int = None
    allow_loc_length: int = None
    allow_loc_width: int = None
    enable: bytes = None
    # 货格数
    total_num: int = None

    @property
    def nums(self):
        """
        兼容一下  total_num是后加的，之前使用wops_loc_template_detail关联计算 货格数，存nums字段
        :return:
        """
        return self.total_num

    @property
    def spec_no(self):
        """
        货格号 (用于 货格 与 模板 关联)
        :return:
        """
        return f'{self.single_length}_{self.single_width}'

    def inner_spec(self, height: int):
        """
        划分的小规格
        :return:
        """
        return f'{height}*{self.allow_loc_length}*{self.allow_loc_width}'

    def inner_spec_short(self):
        """
        划分的小规格
        :return:
        """
        return f'{self.allow_loc_length}*{self.allow_loc_width}'

    def is_valid(self):
        """
        0-不可用，1-可用
        :return:
        """
        return self.enable and int.from_bytes(self.enable, 'little') == 1


class LocTemplateService(object):
    """
    获取 模板相关信息
    """
    __pool: PooledDB = None

    def __init__(self):
        self.__pool = SF_DB().get_pool()

    def get_template_data(self) -> list[LocTemplate]:
        tpl_list: list[LocTemplate] = []
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            data_list = cursor.db_query(
                'SELECT id, single_length, single_width, allow_loc_length, allow_loc_width, total_num, enable '
                'FROM wops_loc_template ')
            for d in data_list:
                t = LocTemplate(**d)
                tpl_list.append(t)
        return tpl_list


class Loc(DBDevice):
    # 主键
    SERIALKEY: str = None
    # 位置信息
    LOC: str = None
    # 排  1:左  2:右
    LINE: int = None
    # 列
    LIE: int = None
    # 层
    LAYER: int = None
    # 长度
    LENGTH: int = None
    # 宽度
    WIDTH: int = None
    # 高度
    HEIGHT: int = None
    # 热度
    HEATCODE: str = None
    # 管理方式(货格 模板号)
    SUPMODE: str = None

    # 热度CODE-VAL值对应关系 ABC->高中低
    __HEATCODE_MAP = {'A': 2, 'B': 1, 'C': 0}

    @property
    def spec_no(self) -> str:
        """
        货格号 (货格长宽高的一种别称?)
        - 忽略: 高度
        - 考虑: 长_宽
        :return:
        """
        return f'{self.LENGTH}_{self.WIDTH}'

    def spec_no_long(self) -> str:
        """
        货格号 (货格长宽高的一种别称?)
        - 忽略: 高度
        - 考虑: 长_宽
        :return:
        """
        return f'{self.HEIGHT}_{self.LENGTH}_{self.WIDTH}'

    @property
    def heat_val(self):
        """
        热度 转换为数值
        :return:
        """
        if self.HEATCODE in self.__HEATCODE_MAP:
            return self.__HEATCODE_MAP[self.HEATCODE]
        return 0


class LocService(object):
    """
    获取位置相关信息 (仅需选取件箱相关数据)
    """
    __pool: PooledDB = None

    def __init__(self):
        self.__pool = SF_DB().get_pool()

    def get_putawayzone(self) -> list[str]:
        """
        获取 巷道信息
        返回 [('ASRS-B01', '201')] 这种
        :return:
        """
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            data_list = cursor.db_query(
                "SELECT distinct BYLANENUM FROM loc where PUTAWAYZONE like CONCAT(%s, '%%') and BYLANENUM is not null ORDER BY BYLANENUM",
                (Config.JX_ZONE_DB_PRE,))
            return [_['BYLANENUM'] for _ in data_list]

    def get_zone_data(self, zone: str) -> list[Loc]:
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            # 单纯根据PUTAWAYZONE 查询有错误数据，再加一个过滤条件
            data_list = cursor.db_query(
                'SELECT SERIALKEY, LOC, LINE, LIE, LAYER, LENGTH, WIDTH, HEIGHT, HEATCODE, SUPMODE '
                'FROM loc '
                'WHERE BYLANENUM = %s AND LENGTH(LOC) = %s AND ABC != %s AND LOCATIONTYPE != %s',
                (zone, 13, 'N', 'TALLY'))
            return [Loc(**_) for _ in data_list]


class LocAllocated(DBDevice):
    # 位置信息
    LOC: str = None
    # 货位占用数量
    loc_allocated: int = None


class LocAllocatedService(object):
    """
    货格 货位占用情况
    """
    __pool: PooledDB = None

    def __init__(self):
        self.__pool = SF_DB().get_pool()

    def get_data(self) -> list[LocAllocated]:
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            data_list = cursor.db_query('''
                                SELECT l.LOC, l.SUPMODE, COUNT(g.id) AS loc_allocated
                                FROM loc l
                                LEFT JOIN flexiblestoragelocgraph g ON l.LOC = g.LOC AND LENGTH(g.sku) > 0 AND LENGTH(g.id) > 0
                                WHERE l.PUTAWAYZONE LIKE 'ASRS-B%%' 
                                GROUP BY l.LOC
                                HAVING loc_allocated > 0''')
            return [LocAllocated(**_) for _ in data_list]


class LocTemplateRatio(DBDevice):
    """
    模板热度
    """
    length: int = None
    width: int = None
    height: int = None
    heat_code: str = None
    ratio: decimal = None

    @property
    def spec(self):
        """
        规格 高*长*宽
        :return:
        """
        return f'{self.height}*{self.length}*{self.width}'


class LocTemplateRatioService(object):
    __pool: PooledDB = None

    def __init__(self):
        self.__pool = SF_DB().get_pool()

    def get_data(self) -> list[LocTemplateRatio]:
        with self.__pool.get_connection() as conn:
            cursor = conn.cursor(DictCursor)
            data_list = cursor.db_query('''
                                SELECT length, width, height, heat_code, ratio
                                FROM wops_loc_template_ratio
                                ''')
            return [LocTemplateRatio(**_) for _ in data_list]


if __name__ == '__main__':
    #
    # lts = LocTemplateService()
    # tpl_list = lts.get_template_data()
    # print(tpl_list)
    #
    # ls = LocService()
    # zone_list = ls.get_putawayzone()
    # print(zone_list)
    #
    # las = LocAllocatedService()
    # las_list = las.get_data()
    # print(las_list)
    lr = LocTemplateRatioService()
    lr_list = lr.get_data()
    print(lr_list)
    pass
